﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace SqlConvertTool.DBContext;

public sealed class OracleDBContext : BaseDBContext
{
    private OracleConnection? Connection;

    protected override DbDataAdapter GetTableAdapter()
    {
        return new OracleDataAdapter("SELECT TABLE_NAME FROM USER_TABLES", Connection);
    }

    protected override DbDataAdapter GetViewsAdapter()
    {
        return new OracleDataAdapter("SELECT VIEW_NAME  From user_views", Connection);
    }

    protected override DbDataAdapter TableDataAdapter(string sql)
    {
        return new OracleDataAdapter(sql, Connection);
    }

    protected override bool OutPutStore()
    {
        throw new NotImplementedException();
    }

    protected override DbConnection ToConnection(string host, string server, string username, string password)
    {
        var conn = string.Format("User Id={2};Password={3};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME={1})))",
            host, server, username, password);
        return Connection = new OracleConnection(conn);
    }

    protected override DbCommand TableSqlCommand(string tbl)
    {
        var sql = $"select dbms_metadata.get_ddl('TABLE','{tbl}') from dual";
        return new OracleCommand(sql, Connection);
    }

    protected override DbCommand ViewSqlCommand(string view)
    {
        var sql = $"select dbms_metadata.get_ddl('VIEW','{view}') from dual";
        return new OracleCommand(sql, Connection);
    }

    protected override DbDataAdapter GetTriggerAdapter()
        => new OracleDataAdapter("SELECT TRIGGER_NAME FROM USER_TRIGGERS", Connection);

    protected override DbCommand TriggerCommand(string trigger)
    {
        var sql = $"SELECT DBMS_METADATA.GET_DDL('TRIGGER', '{trigger}') FROM DUAL";
        return new OracleCommand(sql, Connection);
    }


    protected override DbCommandBuilder GetCommandBulider()
    {
        return new OracleCommandBuilder();
    }

    protected override DbTransaction GetTransaction()
    {
        return Connection!.BeginTransaction();
    }

    protected override DataTable GetData(string tbl)
    {
        var sql = $"SELECT COLUMN_NAME,DATA_TYPE,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '{tbl}'";
        var dt = new DataTable();
        using (var adp = new OracleDataAdapter(sql, Connection))
        {
            adp.Fill(dt);
        }
        var list = new List<string>();
        foreach (DataRow dr in dt.Rows)
        {
            var cols = dr.ItemArray.Cast<string>().ToList();
            if (cols[2].Equals("Y") && cols[1].Contains("CHAR"))
            {
                list.Add($"NVL({cols[0]},'') {cols[0]}");
            }
            else list.Add(cols[0]);
        }
        sql = $"SELECT {string.Join(",", list)} FROM {tbl}";
        dt = new DataTable();
        using (var adp = new OracleDataAdapter(sql, Connection))
        {
            adp.Fill(dt);
        }
        return dt;
    }

    protected override DbDataAdapter GetTypeAdapter()
        => new OracleDataAdapter("SELECT TYPE_NAME  FROM  USER_TYPES", Connection);

    protected override DbCommand TypeCommand(string type)
    {
        var sql = $"SELECT DBMS_METADATA.GET_DDL('TYPE', '{type}') FROM DUAL";
        return new OracleCommand(sql, Connection);
    }

    protected override List<Tuple<string,string>> StoreList(string user)
    {
        var sql = "SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') ORDER BY OBJECT_TYPE";
        var dt = new DataTable();
        using (var adp = new OracleDataAdapter(sql, Connection))
        {
            adp.Fill(dt);
        }
        var list = new List<Tuple<string, string>>();
        foreach (DataRow dr in dt.Rows)
        {
            var sub = dr.ItemArray.Cast<string>().ToList();
            if (sub[1].Equals("PACKAGE"))
            {
                using var cmd = new OracleCommand($"SELECT DBMS_METADATA.GET_DDL('PACKAGE','{sub[0]}') FROM DUAL", Connection);
                list.Add(new Tuple<string, string>(sub[0], Convert.ToString(cmd.ExecuteScalar())!));
            }
            else
            {
                using var cmd = new OracleCommand($"SELECT DBMS_METADATA.GET_DDL('{sub[1]}','{sub[0]}','{user}') FROM DUAL", Connection);
                list.Add(new Tuple<string, string>(sub[0], Convert.ToString(cmd.ExecuteScalar())!));
            }
        }
        return list;
    }
}
